﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffInfo]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffInfo];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffInfo] 
    @StaffID int
AS
BEGIN
SET NOCOUNT ON

SELECT
	a.*,
    datediff(yy, a.birthday, getdate()) as Age,
    (case a.sex when 1 then N'男' else N'女' end) as SexName,
    Convert(nvarchar(10),a.RegistedDate,120) AS RQ,
    b.position_id AS position_id,
    (SELECT Position_name FROM uds_Position c WHERE c.Position_id = b.Position_id) AS Position_Name 
FROM
	uds_staff a,
	uds_staff_in_position b
WHERE
	a.staff_id = @staffid
    and a.staff_id = b.staff_id

END
GO